<?php
/**
* @file $Id: Export.php 578 2007-06-05 19:06:39Z focus-sis $
* @package Focus/SIS
* @copyright Copyright (C) 2006 Andrew Schmadeke. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* Focus/SIS is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
*/

$fields_RET = DBGet(DBQuery("SELECT TITLE,ID,TYPE,SELECT_OPTIONS FROM CUSTOM_FIELDS ORDER BY SORT_ORDER,TITLE"),array(),array('ID'));

include_once('ProgramFunctions/miscExport.fnc.php');

if(count($_REQUEST['students']))
{
	foreach($_REQUEST['students'] as $student_id=>$student)
	{
		$sql = "UPDATE STUDENTS SET ";
		foreach($student as $column_name=>$value)
		{
			if(!is_array($value))
				$sql .= "$column_name='".str_replace("\'","''",str_replace('``','"',$value))."',";
		}
		$sql = substr($sql,0,-1) . " WHERE STUDENT_ID='$student_id'";
		DBQuery($sql);
	}
	unset($_REQUEST['students']);
	unset($_SESSION['_REQUEST_vars']['students']);
}

if($_REQUEST['modname']=='misc/Export.php' || substr($_REQUEST['modname'],0,22)=='Reports/RunReport.php?')
{
	DrawHeader(ProgramTitle());
	Widgets('all');

	$extra['search'] .= '<TABLE><TR><TD align='.ALIGN_RIGHT.' width=40>'._('Include courses active as of').' </TD><TD>'.PrepareDate('','_include_active_date').'</TD></TR>';
	$extra['search'] .= '<TR><TD colspan=2><INPUT type=hidden name=relation></TD></TR></TABLE>';
	$extra['action'] .= ' onsubmit=document.forms[0].relation.value=document.getElementById("relation").value;';
}

$extra['search'] .= '<TABLE><TR><TD align=center colspan=2><TABLE><TR><TD><DIV id=fields_div></DIV></TD></TR></TABLE></TD></TR></TABLE>';
$extra['new'] = true;
$extra['force_search'] = true;

$contacts = array('FATHER'=>_('Parent / Father'),'MOTHER'=>_('Parent / Mother'),'EMERGENCY'=>_('3rd Contact / Emergency'),'CONTACT'=>_('4th Contact'));
$elems = array('NAME'=>_('Name'),'CELL'=>_('Cell Phone'),'WORK'=>_('Work Phone'),'EMPLOYER'=>_('Employer'),'OCCUPATION'=>_('Occupation'),'EMAIL'=>_('Email'));

$_FOCUS['CustomFields'] = true;
if($_REQUEST['fields']['ADDRESS'] || $_REQUEST['fields']['CITY'] || $_REQUEST['fields']['STATE'] || $_REQUEST['fields']['ZIPCODE'] || $_REQUEST['fields']['PHONE'] || $_REQUEST['fields']['MAIL_ADDRESS'] || $_REQUEST['fields']['MAIL_CITY'] || $_REQUEST['fields']['MAIL_STATE'] || $_REQUEST['fields']['MAIL_ZIPCODE'] || $_REQUEST['fields']['PARENTS'])
{
	$extra['SELECT'] .= ',a.ADDRESS_ID,a.ADDRESS,a.CITY,a.STATE,a.ZIPCODE,a.PHONE,COALESCE(a.MAIL_ADDRESS,a.ADDRESS) AS MAIL_ADDRESS,COALESCE(a.MAIL_CITY,a.CITY) AS MAIL_CITY,COALESCE(a.MAIL_STATE,a.STATE) AS MAIL_STATE,COALESCE(a.MAIL_ZIPCODE,a.ZIPCODE) AS MAIL_ZIPCODE';
	$extra['addr'] = true;
	if($_REQUEST['fields']['MAIL_ADDRESS'])
		$extra['STUDENTS_JOIN_ADDRESS'] .= " AND sam.MAILING='Y'";
}
if($_REQUEST['fields']['PARENTS'])
{
	$extra['SELECT'] .= ',ssm.STUDENT_ID AS PARENTS';
	$view_other_RET['ALL_CONTACTS'][1]['VALUE']='Y';
	if($_REQUEST['relation']!='')
	{
		$_FOCUS['makeParents'] = $_REQUEST['relation'];
		$extra['STUDENTS_JOIN_ADDRESS'] .= " AND EXISTS (SELECT '' FROM STUDENTS_JOIN_PEOPLE sjp WHERE sjp.ADDRESS_ID=sam.ADDRESS_ID AND LOWER(sjp.STUDENT_RELATION) LIKE '".strtolower($_REQUEST['relation'])."%') ";
	}
}
$extra['SELECT'] .= ',ssm.NEXT_SCHOOL,ssm.CALENDAR_ID,ssm.SYEAR,ssm.START_DATE,ssm.ENROLLMENT_CODE,ssm.END_DATE,ssm.DROP_CODE,s.*';
if($_REQUEST['fields']['FIRST_INIT'])
	$extra['SELECT'] .= ',substr(s.FIRST_NAME,1,1) AS FIRST_INIT';

if(!$extra['functions'])
	$extra['functions'] = array('NEXT_SCHOOL'=>'_makeNextSchool','CALENDAR_ID'=>'_makeCalendar','SCHOOL_ID'=>'GetSchool','PARENTS'=>'makeParents','START_DATE'=>'ProperDate','END_DATE'=>'ProperDate','ENROLLMENT_CODE'=>'_getEnrollmentCode','DROP_CODE'=>'_getEnrollmentCode');

if($_REQUEST['search_modfunc']=='list' && !$_FOCUS['modules_search'])
{
	if(!$fields_list)
	{
		$fields_list = array('FULL_NAME'=>_('Last, First M'),'FIRST_NAME'=>_('First'),'FIRST_INIT'=>_('First Initial'),'LAST_NAME'=>_('Last'),'MIDDLE_NAME'=>_('Middle'),'NAME_SUFFIX'=>_('Suffix'),'STUDENT_ID'=>_('Student ID'),'GRADE_ID'=>_('Grade'),'SCHOOL_ID'=>_('School'),'NEXT_SCHOOL'=>_('Rolling / Retention Options'),'CALENDAR_ID'=>_('Calendar'),'START_DATE'=>_('Attendance Start Date'),'ENROLLMENT_CODE'=>_('Enrollment Code'),'END_DATE'=>_('Drop Date'),'DROP_CODE'=>_('Drop Code'),'USERNAME'=>_('Username'),'PASSWORD'=>_('Password'),'ADDRESS'=>_('Address'),'CITY'=>_('City'),'STATE'=>_('State'),'ZIPCODE'=>_('Zip Code'),'PHONE'=>_('Home Phone'),'MAIL_ADDRESS'=>_('Mailing Address'),'MAIL_CITY'=>_('Mailing City'),'MAIL_STATE'=>_('Mailing State'),'MAIL_ZIPCODE'=>_('Mailing Zipcode'),'PARENTS'=>_('Contacts'));
		if($extra['field_names'])
			$fields_list += $extra['field_names'];
		
		$periods_RET = DBGet(DBQuery("SELECT TITLE,PERIOD_ID,SHORT_NAME FROM SCHOOL_PERIODS WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."' ORDER BY SORT_ORDER"));
		foreach($periods_RET as $period)
			$fields_list['PERIOD_'.$period['PERIOD_ID']] = $period['TITLE'].' '._('Teacher - Room');

		foreach($contacts as $contact=>$contact_title)
		{
			foreach($elems as $elem=>$elem_title)
				$fields_list[$contact.'_'.$elem] = $contact_title.': '.$elem_title;
		}
/*
		$calculations_RET = DBGet(DBQuery("SELECT ID,TITLE,URL FROM SAVED_CALCULATIONS ORDER BY TITLE"));
		foreach($calculations_RET as $calc)
		{
			$fields_list['CALCULATION_'.$calc['ID']] = $calc['TITLE'];
			$fields_list['GRAPH_'.$calc['ID']] = $calc['TITLE'];
		}
*/
	}
	
	foreach($fields_RET as $id=>$field)
	{
		if(!$fields_list['CUSTOM_'.$id])
			$fields_list['CUSTOM_'.$id] = $field[1]['TITLE'];
	}
	
	foreach($periods_RET as $period)
	{
		if($_REQUEST['month_include_active_date'])
			$date = $_REQUEST['day_include_active_date'].'-'.$_REQUEST['month_include_active_date'].'-'.$_REQUEST['year_include_active_date'];
		else
			$date = DBDate();

		if($_REQUEST['fields']['PERIOD_'.$period['PERIOD_ID']]=='Y')
		{
			if(UserSchool() && $_REQUEST['_search_all_schools']!='Y')
			{	
				$schools_extra = " SCHOOL_ID='".UserSchool()."' ";
				$periods_extra = " cp.PERIOD_ID='".$period['PERIOD_ID']."'";
			}
			else
			{
				$schools = substr(str_replace(",","','",User('SCHOOLS')),2,-2);
				if($schools)
					$schools_extra .= " SCHOOL_ID IN ($schools) ";
				else
					$schools_extra = 'true';

				if($period['SHORT_NAME'])
					$short_names_RET = DBGet(DBQuery("SELECT PERIOD_ID FROM SCHOOL_PERIODS WHERE SHORT_NAME='".$period['SHORT_NAME']."' AND ".$schools_extra));
				$periods_extra = " cp.PERIOD_ID IN (";
				foreach($short_names_RET as $id)
					$periods_extra .= "'".$id['PERIOD_ID']."',";
				$periods_extra = substr($periods_extra,0,-1);
				$periods_extra .= ") ";
			}
			$extra['SELECT'] .= ',(SELECT COALESCE(st.FIRST_NAME,\' \')||\' \'||COALESCE(st.LAST_NAME,\' \')||\' - \'||COALESCE(cp.ROOM,\' \') FROM USERS st,SCHEDULE ss,COURSE_PERIODS cp WHERE ss.STUDENT_ID=ssm.STUDENT_ID AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND cp.TEACHER_ID=st.STAFF_ID AND '.$periods_extra.' AND (\''.$date.'\' BETWEEN ss.START_DATE AND ss.END_DATE OR \''.$date.'\'>=ss.START_DATE) ORDER BY ss.START_DATE LIMIT 1) AS PERIOD_'.$period['PERIOD_ID'];
		}
	}

	foreach($contacts as $contact=>$contact_title)
	{
		foreach($elems as $elem=>$elem_title)
		{
			if($_REQUEST['fields'][$contact.'_'.$elem]=='Y')
			{
				$contact_fields = true;
				$extra['SELECT'] .= ',NULL AS '.$contact.'_'.$elem;
				$extra['functions'][strtoupper($contact.'_'.$elem)] = '_makeContactInfo';
			}
		}
	}

	if($contact_fields===true)
	{
		$elem_list = '';
		foreach($elems as $elem=>$elem_title)
			$elem_list .= ",'".$elem_title."'";
		$elem_list = substr($elem_list,1);

		$extra2 = $extra;
		$extra2['NoSearchTerms'] = true;
		$extra2['SELECT_ONLY'] = 'ssm.STUDENT_ID,p.PERSON_ID,p.FIRST_NAME||\' \'||p.LAST_NAME AS NAME,sjp.STUDENT_RELATION,pjc.TITLE,pjc.VALUE,';
		// addr.PHONE
		$extra2['SELECT_ONLY'] .= db_case(array('LOWER(STUDENT_RELATION)',"'"._('father')."'".' OR LOWER(STUDENT_RELATION)='."'"._('dad')."'","'father'","'"._('mother')."'".' OR LOWER(STUDENT_RELATION)='."'"._('mom')."'","'mother'","'"._('parent')."'","'parent'","'"._('emergency').'%\'',"'emergency'","'other'")).' AS CONTACT_TYPE ';
		// LEFT OUTER JOIN ON THE ADDRESS TABLE IF ADDRESSES ARE INCLUDED IN THE ADV REPORT
		if($extra['addr'])
		{
			$extra2['FROM'] .= 'LEFT OUTER JOIN STUDENTS_JOIN_PEOPLE sjp on (sjp.STUDENT_ID=ssm.STUDENT_ID) LEFT OUTER JOIN PEOPLE p ON (p.PERSON_ID=sjp.PERSON_ID) LEFT OUTER JOIN PEOPLE_JOIN_CONTACTS pjc ON (pjc.PERSON_ID=p.PERSON_ID AND ('.$elem_list.')) ';
			$extra2['group'] = array('STUDENT_ID','ADDRESS_ID','CONTACT_TYPE');
		}
		else
		{
			$extra2['FROM'] .= 'LEFT OUTER JOIN STUDENTS_JOIN_PEOPLE sjp on (sjp.STUDENT_ID=ssm.STUDENT_ID) LEFT OUTER JOIN PEOPLE p ON (p.PERSON_ID=sjp.PERSON_ID) LEFT OUTER JOIN PEOPLE_JOIN_CONTACTS pjc ON (pjc.PERSON_ID=p.PERSON_ID AND ('.$elem_list.')) ';
			$extra2['group'] = array('STUDENT_ID','CONTACT_TYPE');
		}
		$extra2['functions'] = array();
		$extra2['link'] = array();
		$contacts_RET = GetStuList($extra2);	
	}
	/*
	foreach($calculations_RET as $calc)
	{
		if($_REQUEST['fields']['CALCULATION_'.$calc['ID']]=='Y')
		{	
			$extra['SELECT'] .= ',ssm.STUDENT_ID AS CALCULATION_'.$calc['ID'];
			$extra['functions']['CALCULATION_'.$calc['ID']] = '_runCalc';
			include_once('ProgramFunctions/ReportsCalculations.php');
		}
		if($_REQUEST['fields']['GRAPH_'.$calc['ID']]=='Y')
		{
			if($_REQUEST['fields']['CALCULATION_'.$calc['ID']]!='Y')
			{
				$extra['SELECT'] .= ',ssm.STUDENT_ID AS CALCULATION_'.$calc['ID'];
				$extra['functions']['CALCULATION_'.$calc['ID']] = '_runCalc';
				include_once('ProgramFunctions/ReportsCalculations.php');
			}
			$extra['SELECT'] .= ',ssm.STUDENT_ID AS GRAPH_'.$calc['ID'];
			$graph = true;
		}
	}
	*/
	if($_REQUEST['fields'])
	{
		foreach($_REQUEST['fields'] as $field=>$on)
		{
			$columns[$field] = $fields_list[$field];
			if($_REQUEST['modname']=='misc/Export.php' || substr($_REQUEST['modname'],0,22)=='Reports/RunReport.php?')
			{
				if($fields_RET[substr($field,7)][1]['TYPE']=='date' && !$extra['functions'][$field])
					$extra['functions'][$field] = 'ProperDate';
				elseif(($fields_RET[substr($field,7)][1]['TYPE']=='text' || $fields_RET[substr($field,7)][1]['TYPE']=='multiple') && !$extra['functions'][$field])
					$extra['functions'][$field] = '_makeTextInput';
				elseif($fields_RET[substr($field,7)][1]['TYPE']=='select' && !$extra['functions'][$field])
					$extra['functions'][$field] = '_makeSelectInput';
				elseif($fields_RET[substr($field,7)][1]['TYPE']=='radio' && !$extra['functions'][$field])
					$extra['functions'][$field] = '_makeCheckboxInput';
			}
		}
		$RET = GetStuList($extra);
		if($extra['array_function'] && function_exists($extra['array_function']))
			$extra['array_function']($RET);
		if($graph===true)
		{
			_createGraphs($RET);
		}
		
		echo '<FORM action='.PreparePHP_SELF().' method=POST>';
		DrawHeader('',SubmitButton(_('Save')));
		ListOutput($RET,$columns,_('Student'),_('Students'),array(),array(),$extra['LO_options']);
		echo '</FORM>';
	}
}
else
{
	$categories_RET = DBGet(DBQuery("SELECT ID,TITLE FROM STUDENT_FIELD_CATEGORIES ORDER BY SORT_ORDER,TITLE"),array(),array('ID'));
	$custom_RET = DBGet(DBQuery("SELECT TITLE,ID,TYPE,CATEGORY_ID FROM CUSTOM_FIELDS WHERE TYPE!='holder' AND TYPE!='log' ORDER BY SORT_ORDER,TITLE"),array(),array('CATEGORY_ID'));

	$general_title = $categories_RET[1][1]['TITLE'];
	$address_title = $categories_RET[3][1]['TITLE'];
	if(!$fields_list)
	{
		if(AllowUse('Students/Student.php&category_id=1'))
			$fields_list[$general_title] = array('FULL_NAME'=>_('Last, First M'),'FIRST_NAME'=>_('First'),'FIRST_INIT'=>_('First Initial'),'LAST_NAME'=>_('Last'),'MIDDLE_NAME'=>_('Middle'),'NAME_SUFFIX'=>_('Suffix'),'STUDENT_ID'=>_('Student ID'),'GRADE_ID'=>_('Grade'),'SCHOOL_ID'=>_('School'),'NEXT_SCHOOL'=>_('Rolling / Retention Options'),'START_DATE'=>_('Attendance Start Date'),'ENROLLMENT_CODE'=>_('Enrollment Code'),'END_DATE'=>_('Drop Date'),'DROP_CODE'=>_('Drop Code'),'CALENDAR_ID'=>_('Calendar'),'USERNAME'=>_('Username'),'PASSWORD'=>_('Password'));
		if(!AllowEdit('Students/Student.php&category_id=1'))
			unset($fields_list[$general_title]['PASSWORD']);

		if(AllowUse('Students/Student.php&category_id=3'))
			$fields_list[$address_title] = array('ADDRESS'=>_('Address'),'CITY'=>_('City'),'STATE'=>_('State'),'ZIPCODE'=>_('Zip Code'),'PHONE'=>_('Home Phone'),'MAIL_ADDRESS'=>_('Mailing Address'),'MAIL_CITY'=>_('Mailing City'),'MAIL_STATE'=>_('Mailing State'),'MAIL_ZIPCODE'=>_('Mailing Zipcode'),'PARENTS'=>_('Contacts'),'CONTACT_INFO'=>_('Contact Info Rollover'));
		if($extra['field_names'])
			$fields_list[$general_title] += $extra['field_names'];
	}
	
	foreach($custom_RET as $category_id=>$fields)
	{
		if(AllowUse('Students/Student.php&category_id='.$category_id))
		{
			foreach($fields as $field)
				$fields_list[$categories_RET[$category_id][1]['TITLE']]['CUSTOM_'.$field['ID']] = str_replace("'",'&rsquo;',$field['TITLE']);
		}
	}
	
	if(AllowUse('Scheduling/Schedule.php'))
	{
		$periods_RET = DBGet(DBQuery("SELECT TITLE,PERIOD_ID FROM SCHOOL_PERIODS WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."' ORDER BY SORT_ORDER"));
		foreach($periods_RET as $period)
			$fields_list[_('Schedule')]['PERIOD_'.$period['PERIOD_ID']] = $period['TITLE'].' '._('Teacher - Room');
	}
/*	THIS CODE REFERENCES CALCULATIONS FEATURES WHICH AREN'T YET IMPLEMENTED
	$calculations_RET = DBGet(DBQuery("SELECT ID,TITLE,URL FROM SAVED_CALCULATIONS ORDER BY TITLE"));
	foreach($calculations_RET as $calc)
	{
		$fields_list[_('Calculations')]['CALCULATION_'.$calc['ID']] = $calc['TITLE'];
		$fields_list[_('Calculations')]['GRAPH_'.$calc['ID']] = $calc['TITLE'].' '._('Graph');
	}
*/
	DrawHeader('<OL><SPAN id=names_div></SPAN></OL>');
	echo '<TABLE>';
	//PopTable('header','Fields');
	$extra['second_col'] .= '<fieldset><legend>'._('Fields to include in report').'</legend><TABLE><TR>';
	foreach($fields_list as $category=>$fields)
	{
		$extra['second_col'] .= '<TD colspan=2><b>'.$category.'<BR><HR></b></TD></TR><TR>';
		foreach($fields as $field=>$title)
		{
			$i++;
			$extra['second_col'] .= '<TD><INPUT type=checkbox onclick="addHTML(\'<LI>'.$title.'</LI>\',\'names_div\',false);addHTML(\'<INPUT type=hidden name=fields['.$field.'] value=Y>\',\'fields_div\',false);this.disabled=true">'.$title.($field=='PARENTS'?'<BR>(<small>'._('Relation').': </small><input type=text id=relation name=relation size=8>)':'').'</TD>';
			if($i%2==0)
				$extra['second_col'] .= '</TR><TR>';
		}	
		if($i%2!=0)
		{
			$extra['second_col'] .= '<TD></TD></TR><TR>';
			$i++;
		}
		if($category==$address_title)
		{
			$extra['second_col'] .= '<TD colspan=2><b>'._('Contact Information').'</b><BR><HR></TD></TR><TR><TD colspan=2>';
			$extra['second_col'] .= '<TABLE width=100%>';
			$extra['second_col'] .= '<TR>';
			foreach($contacts as $contact=>$title)
			{
				$extra['second_col'] .= '<TD><b>'.$title.'</b></TD>';
			}
			$extra['second_col'] .= '</TR>';
			foreach($elems as $elem=>$elem_title)
			{
				$extra['second_col'] .= '<TR>';
				foreach($contacts as $contact=>$contact_title)
				{
					$extra['second_col'] .= '<TD><INPUT type=checkbox onclick="addHTML(\'<LI>'.$contact_title.': '.$elem_title.'</LI>\',\'names_div\',false);addHTML(\'<INPUT type=hidden name=fields['.$contact.'_'.$elem.'] value=Y>\',\'fields_div\',false);this.disabled=true">'.$elem_title.'</TD>';
				}
				$extra['second_col'] .= '</TR>';
			}
			$extra['second_col'] .= '</TABLE></TD></TR><TR>';
				
		}
	}
	$extra['second_col'] .= '</TR></TABLE></fieldset>';
	//PopTable('footer');
	Search('student_id',$extra);
}
?>